package com.personnel.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.personnel.dao.IYgUserDao;
import com.personnel.db.C3p0Util1;
import com.personnel.entity.YgUser;

public class YgUserDao implements IYgUserDao{

	@Override
	public boolean addYgUser(YgUser yg) throws SQLException {
		Connection conn = null;
		PreparedStatement pstm = null;
		
		try {
			conn = C3p0Util1.getConn();
			
			conn.setAutoCommit(false);
			
			String sql = " insert into t_yg(ygbh,ygxm,ygxb,csny,zgxe,zgxw,byyx,wyzl,wynl,sfzh,ygxs,bm_id,g_id,hyzk,zzmm,mz,jg,lxdh) "
					+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
			 
			pstm = conn.prepareStatement(sql);
			
			pstm.setInt(1, yg.getYgbh());
			pstm.setString(2, yg.getYgxm());
			pstm.setInt(3, yg.getYgxb());
			pstm.setString(4, yg.getCsny());
			pstm.setString(5, yg.getZgxe());
			pstm.setString(6, yg.getZgxw());
			pstm.setString(7, yg.getByyx());
			pstm.setString(8, yg.getWyzl());
			pstm.setString(9, yg.getWynl());
			pstm.setString(10, yg.getSfzh());
			pstm.setString(11, yg.getYgxm());
			pstm.setInt(12, yg.getBm_id());
			pstm.setInt(13, yg.getG_id());
			pstm.setString(14, yg.getHyzk());
			pstm.setString(15, yg.getZzmm());
			pstm.setString(16, yg.getMz());
			pstm.setString(17, yg.getJg());
			pstm.setString(18, yg.getLxdh());
			
			int x = pstm.executeUpdate();
			conn.commit();
			return x == 1;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
			conn.rollback();
			conn.setAutoCommit(true);
			
			return false;
		}finally {
			C3p0Util1.close(pstm);
			C3p0Util1.close(conn);
			
		}
		
		
	}

	@Override
	public List<YgUser> queryAllYgUsers(int curPage, int count) throws Exception {
		Connection conn = null;
		PreparedStatement pstm = null;
		
		conn = C3p0Util1.getConn();
		
		String sql = " select * from t_yg limit ?,? ";
		
		pstm = conn.prepareStatement(sql);
		pstm.setInt(1, (curPage - 1) * count);
		pstm.setInt(2, count);
		ResultSet rs = pstm.executeQuery();
		ArrayList<YgUser> list = new ArrayList<>();
		while(rs.next()) {
			YgUser x = new YgUser();
			x.setY_id(rs.getInt("y_id"));
			x.setYgbh(rs.getInt("ygbh"));
			x.setYgxm(rs.getString("ygxm"));
			x.setYgxb(rs.getInt("ygxb"));
			x.setCsny(rs.getString("csny"));
			x.setZgxe(rs.getString("zgxe"));
			x.setZgxw(rs.getString("zgxw"));
			x.setByyx(rs.getString("byyx"));
			x.setWyzl(rs.getString("wyzl"));
			x.setWynl(rs.getString("wynl"));
			x.setSfzh(rs.getString("sfzh"));
			x.setYgxs(rs.getString("ygxs"));
			x.setBm_id(rs.getInt("bm_id"));
			x.setG_id(rs.getInt("g_id"));
			x.setHyzk(rs.getString("hyzk"));
			x.setZzmm(rs.getString("zzmm"));
			x.setMz(rs.getString("mz"));
			x.setJg(rs.getString("jg"));
			x.setLxdh(rs.getString("lxdh"));
			list.add(x);
			
		}
		
		return list;
	}

	@Override
	public int count() throws SQLException {
		Connection conn = null;
		PreparedStatement pstm = null;
		conn = C3p0Util1.getConn();
		String sql = " select count(*) from t_yg ";
		pstm = conn.prepareStatement(sql);
		ResultSet rs = pstm.executeQuery();
		
		int count = 0;
		
		if(rs.next()) {
			count = rs.getInt(1);
		}
		return count;
	}

	@Override
	public YgUser queryYgUserById(int y_id) {
		Connection conn = null;
		PreparedStatement pstm = null;
		
		try {
			conn = C3p0Util1.getConn();
			String sql = " select * from t_yg where y_id = ? ";
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, y_id);
			ResultSet rs = pstm.executeQuery();
			
			if(!rs.next()) return null;
			
			YgUser x = new YgUser();
			x.setY_id(y_id);
			x.setYgbh(rs.getInt("ygbh"));
			x.setYgxm(rs.getString("ygxm"));
			x.setYgxb(rs.getInt("ygxb"));
			x.setCsny(rs.getString("csny"));
			x.setZgxe(rs.getString("zgxe"));
			x.setZgxw(rs.getString("zgxw"));
			x.setByyx(rs.getString("byyx"));
			x.setWyzl(rs.getString("wyzl"));
			x.setWynl(rs.getString("wynl"));
			x.setSfzh(rs.getString("sfzh"));
			x.setYgxs(rs.getString("ygxs"));
			x.setBm_id(rs.getInt("bm_id"));
			x.setG_id(rs.getInt("g_id"));
			x.setHyzk(rs.getString("hyzk"));
			x.setZzmm(rs.getString("zzmm"));
			x.setMz(rs.getString("mz"));
			x.setJg(rs.getString("jg"));
			x.setLxdh(rs.getString("lxdh"));
			return x;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		
		
	}

	@Override
	public boolean updateYgUser(YgUser yg) {
		Connection conn = null;
		PreparedStatement pstm = null;
		
		try {
			conn = C3p0Util1.getConn();
			String sql = " update t_yg set ygbh=?,ygxm=?,ygxb=?,csny=?,zgxe=?,zgxw=?,byyx=?,wyzl=?,wynl=?,sfzh=?,ygxs=?,bm_id=?,g_id=?,hyzk=?,zzmm=?,mz=?,jg=?,lxdh=? where y_id=? ";
			pstm = conn.prepareStatement(sql);
			
			pstm.setInt(1, yg.getYgbh());
			pstm.setString(2, yg.getYgxm());
			pstm.setInt(3, yg.getYgxb());
			pstm.setString(4, yg.getCsny());
			pstm.setString(5, yg.getZgxe());
			pstm.setString(6, yg.getZgxw());
			pstm.setString(7, yg.getByyx());
			pstm.setString(8, yg.getWyzl());
			pstm.setString(9, yg.getWynl());
			pstm.setString(10, yg.getSfzh());
			pstm.setString(11, yg.getYgxm());
			pstm.setInt(12, yg.getBm_id());
			pstm.setInt(13, yg.getG_id());
			pstm.setString(14, yg.getHyzk());
			pstm.setString(15, yg.getZzmm());
			pstm.setString(16, yg.getMz());
			pstm.setString(17, yg.getJg());
			pstm.setString(18, yg.getLxdh());
			pstm.setInt(19, yg.getY_id());
			
			int x = pstm.executeUpdate();
			return x == 1;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		
		
	}

	@Override
	public boolean deleteYgUser(int y_id) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = C3p0Util1.getConn();
			String sql = " delete from t_yg where y_id = ? ";
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, y_id);
			pstm.executeUpdate();
//			conn.commit();
			pstm.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			C3p0Util1.close(pstm);
			C3p0Util1.close(conn);
		}
		return false;
	}

}
